﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Data;

/// <summary>
/// Summary description for Cusdoc
/// </summary>
public class CusdocModel
{
    public long ID = 0;

    public long FILENO = 0;                      //1
    public string MBKGNO = "";//so booking hang tau         =2
    public string HBLNO = "";//so van don cua BV            =3
    public string MBLNO = "";//so van don cua hang tau      =4      
    public string HBLSHPR = "";//ten nguoi gui hang         =5
    public string HBLCNEE = "";//ten nguoi nhan hang        =6
    public string HBLNTF = "";// ten nguoi nhan thong bao   =7

    public string VSSL = "";//ten con tau                   =8
    public string VOYNO = "";//so chuyen tau                =9      
    public long PORID = 0;//dia diem nhan hang              =10
    public string PORNAME = ""; //EXT - Ten dia diem nhan hang
    public long POLID = 0;//cang chat hang                  =11
    public string POLNAME = "";//EXT - Ten dia diem chat hang
    public DateTime SHONBOARD = DateTime.Now;//ngay len tau =12
    public long PodID = 0;//cang giao hang                  =13
    public string PODNAME = "";//EXT - Ten cang giao hang
    public long DESTID = 0;// cang den                      =14
    public string DESTNAME = "";//EXT - Ten cang den

    public string DOGMN = "";//dien giai hang hoa           =22
    public string TypeOfMovement = "";//dieu kien van chuyen=23
    public string TOP = "";//loai cuoc thanh toan           =24
    public long PAYABLEAT = 0;//noi thanh toan cuoc         =25
    public string PAYABLEATNAME = "";//EXT noi thanh toan cuoc
    public long POISS = 0;//noi phat hanh bill              =26
    public string POISSNAME = "";//EXT noi phat hanh bill
    public string NOORI = "";//so luong van don goc         =27
    public DateTime DOISS = DateTime.Now;//ngay phat hanh bill=28
    public string AGDLVR = "";//ten dai ly                  =29
    public string CARRN = "";//ten hang tau                 =30    

    public string MBLSHPR = "";//ten nguoi gui hang tren MBL=31
    public string MBLCNEE = "";//ten nguoi nhan hang tren MBL=32
    public string MBLNTF = "";//ten nguoi thong bao tren MBL=33
    public string BOOKER = "";//nguoi dat cho               =34
    public int CTRNO = 0;//so hop dong                      =35
    public DateTime CTRVALID = DateTime.Now;//ngay het han hd=36
    public string CTRNAME = "";//ten hop dong               =37

    public double RATE = 0;// gia cuoc                      =43
    public string UNIT = "";//don vi                        =44
    public string CURR = "";//loai tien                     =45
    public double DVALUE = 0;//tong gia tri                 =46
    public string STUFF = "";//dong hang                    =47

    public DateTime CreatedDate = DateTime.Now;
    public long UserID = 0;
    public int MBLType = 0;
    public int HBLType = 0;
    public string TOPMBL = "";                            //=48
    public DateTime BACKDATE = DateTime.MinValue;         //=49
    public string NOTE = "";
    public const string OriMBL = "Ori.MBL";
    public const string SurMBL = "Sur.MBL";
    public const string OriHBL = "Ori.HBL";
    public const string SurHBL = "Sur.HBL";

    //Report
    public string SI_ATTN = "";
    public string SI_NOTED = "";
    public string SI_COMMENT = "";
    public DateTime SI_UPDATE = DateTime.Now;
    public string TRANS_ATTN = "";
    public DateTime TRANS_UPDATE = DateTime.Now;
    public string TRANS_ACC = "";
    public string ARRIAL_ATTN = "";
    public DateTime ARRIAL_UPDATE = DateTime.Now;
    public string ARRIAL_ACC = "";

    public string CARRCODE = "";//ma hang tau                 
    public string SERIBILL = "";//seri bill                 
    public CusdocModel()
    {

    }
    public void Save(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_CusDoc";
        if (IsFileNumberExisting(this.FILENO, this.ID, cn, trans) > 0)
        {
            GV.ErrorMessage = "The File Number is Existed";
            return;
        }
        if (ID <= 0) // new object, we insert new record to database
        {
            strSQL = "Insert into " + strTableName + " (" +

                            "`FILENO`," +
                            "`MBKGNO`," +
                            "`HBLNO`," +
                            "`MBLNO`," +
                            "`HBLSHPR`," +
                            "`HBLCNEE`," +
                            "`HBLNTF`," +
                            "`VSSL`," +
                            "`VOYNO`," +
                            "`PORID`," +
                            "`POLID`," +
                            "`SHONBOARD`," +
                            "`PodID`," +
                            "`DESTID`," +
                            "`DOGMN`," +
                            "`TypeOfMovement`," +
                            "`TOP`," +
                            "`PAYABLEAT`," +
                            "`POISS`," +
                            "`NOORI`," +
                            "`DOISS`," +
                            "`AGDLVR`," +
                            "`CARRN`," +
                            "`MBLSHPR`," +
                            "`MBLCNEE`," +
                            "`MBLNTF`," +
                            "`BOOKER`," +
                            "`CTRNO`," +
                            "`CTRVALID`," +
                            "`CTRNAME`," +
                            "`RATE`," +
                            "`UNIT`," +
                            "`CURR`," +
                            "`DVALUE`," +
                            "`STUFF`," +
                            "`CreatedDate`," +
                            "`UserID`," +
                            "`MBLType`," +
                            "`HBLType`," +
                            "`TOPMBL`," +
                            "`NOTE`," +
                            "`BACKDATE`," +
                            "`PORNAME`," +
                            "`POLNAME`," +
                            "`PODNAME`," +
                            "`DESTNAME`," +
                            "`POISSNAME`," +
                            "`PAYABLEATNAME`" +
                            ",`CARRCODE`" +
                            ",`SERIBILL`" +
                         ") " +
                     " values(" +
                        "" + FILENO.ToString() + "," +
                        "'" + MBKGNO.ToString() + "'," +
                        "'" + DBModule.RefineString(HBLNO) + "'," +
                        "'" + DBModule.RefineString(MBLNO) + "'," +
                        "'" + DBModule.RefineString(HBLSHPR) + "'," +
                        "'" + DBModule.RefineString(HBLCNEE) + "'," +
                        "'" + DBModule.RefineString(HBLNTF) + "'," +
                        "'" + DBModule.RefineString(VSSL) + "'," +
                        "'" + DBModule.RefineString(VOYNO) + "'," +
                        "" + PORID.ToString() + "," +
                        "" + POLID.ToString() + "," +
                        "" + DBModule.RefineDatetime(SHONBOARD) + "," +
                        "" + PodID.ToString() + "," +
                        "" + DESTID.ToString() + "," +
                        "'" + DBModule.RefineString(DOGMN) + "'," +
                        "'" + DBModule.RefineString(TypeOfMovement) + "'," +
                        "'" + DBModule.RefineString(TOP) + "'," +
                        "" + PAYABLEAT + "," +
                        "" + POISS + "," +
                        "'" + DBModule.RefineString(NOORI) + "'," +
                        "" + DBModule.RefineDatetime(DOISS) + "," +
                        "'" + DBModule.RefineString(AGDLVR) + "'," +
                        "'" + DBModule.RefineString(CARRN) + "'," +
                        "'" + DBModule.RefineString(MBLSHPR) + "'," +
                        "'" + DBModule.RefineString(MBLCNEE) + "'," +
                        "'" + DBModule.RefineString(MBLNTF) + "'," +
                        "'" + DBModule.RefineString(BOOKER) + "'," +
                        "" + CTRNO.ToString() + "," +
                        "" + DBModule.RefineDatetime(CTRVALID) + "," +
                        "'" + DBModule.RefineString(CTRNAME) + "'," +
                        "" + RATE.ToString() + "," +
                        "'" + DBModule.RefineString(UNIT) + "'," +
                        "'" + DBModule.RefineString(CURR) + "'," +
                        "" + DVALUE.ToString() + "," +
                        "'" + DBModule.RefineString(STUFF) + "'," +
                        "" + DBModule.RefineDatetime(CreatedDate) + "," +
                        "" + UserID.ToString() + "," +
                        "" + MBLType.ToString() + "," +
                        "'" + HBLType + "'," +
                        "'" + DBModule.RefineString(TOPMBL) + "'," +
                        "'" + DBModule.RefineString(NOTE) + "'," +
                        "" + DBModule.RefineDatetime(BACKDATE) + "," +
                        "'" + DBModule.RefineString(PORNAME) + "'," +
                        "'" + DBModule.RefineString(POLNAME) + "'," +
                        "'" + DBModule.RefineString(PODNAME) + "'," +
                        "'" + DBModule.RefineString(DESTNAME) + "'," +
                        "'" + DBModule.RefineString(POISSNAME) + "'," +
                        "'" + DBModule.RefineString(PAYABLEATNAME) + "'" +
                        ",'" + DBModule.RefineString(CARRCODE) + "'" +
                        ",'" + DBModule.RefineString(SERIBILL) + "'" +
                         ")";
            // run SQL statement
            //throw new Exception(strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("CusDoc", "add", cn, trans);
            //GV.Response.Write(strSQL);
            string strcusdocid = DBModule.ExecuteQueryForOneResult("SELECT MAX(id) AS maxID FROM tbl_Cusdoc", GV.cn, GV.trans);
            if (strcusdocid != null)
                this.ID = long.Parse(strcusdocid);
        }
        else // edit object, we update old record in database
        {
            // build SQL statement
            strSQL = "Update " + strTableName + " set " +
                            "FILENO=" + FILENO.ToString() + "," +
                            "MBKGNO='" + MBKGNO + "'," +
                            "HBLNO='" + DBModule.RefineString(HBLNO.ToString()) + "'," +
                            "MBLNO='" + DBModule.RefineString(MBLNO) + "'," +
                            "HBLSHPR='" + DBModule.RefineString(HBLSHPR) + "'," +
                            "HBLCNEE='" + DBModule.RefineString(HBLCNEE) + "'," +
                            "HBLNTF='" + DBModule.RefineString(HBLNTF) + "'," +
                            "VSSL='" + DBModule.RefineString(VSSL) + "'," +
                            "VOYNO='" + DBModule.RefineString(VOYNO) + "'," +
                            "PORID='" + PORID.ToString() + "'," +
                            "POLID='" + POLID.ToString() + "'," +
                            "SHONBOARD=" + DBModule.RefineDatetime(SHONBOARD) + "," +
                            "PodID=" + PodID.ToString() + "," +
                            "DESTID=" + DESTID.ToString() + "," +
                            "DOGMN='" + DBModule.RefineString(DOGMN) + "'," +
                            "TypeOfMovement='" + DBModule.RefineString(TypeOfMovement) + "'," +
                            "TOP='" + DBModule.RefineString(TOP) + "'," +
                            "PAYABLEAT=" + PAYABLEAT + "," +
                            "POISS=" + POISS + "," +
                            "NOORI='" + DBModule.RefineString(NOORI) + "'," +
                            "DOISS=" + DBModule.RefineDatetime(DOISS) + "," +
                            "AGDLVR='" + DBModule.RefineString(AGDLVR) + "'," +
                            "CARRN='" + DBModule.RefineString(CARRN) + "'," +
                            "MBLSHPR='" + DBModule.RefineString(MBLSHPR) + "'," +
                            "MBLCNEE='" + DBModule.RefineString(MBLCNEE) + "'," +
                            "MBLNTF='" + DBModule.RefineString(MBLNTF) + "'," +
                            "BOOKER='" + DBModule.RefineString(BOOKER) + "'," +
                            "CTRNO='" + CTRNO.ToString() + "'," +
                            "CTRVALID=" + DBModule.RefineDatetime(CTRVALID) + "," +
                            "CTRNAME='" + DBModule.RefineString(CTRNAME) + "'," +
                            "RATE='" + RATE.ToString() + "'," +
                            "UNIT='" + DBModule.RefineString(UNIT) + "'," +
                            "CURR='" + DBModule.RefineString(CURR) + "'," +
                            "DVALUE='" + DVALUE.ToString() + "'," +
                            "STUFF='" + DBModule.RefineString(STUFF) + "'," +

                            "MBLType=" + MBLType.ToString() + "," +
                            "HBLType=" + HBLType.ToString() + "," +
                            "TOPMBL='" + DBModule.RefineString(TOPMBL) + "'," +
                            "NOTE='" + DBModule.RefineString(NOTE) + "'," +
                            "BACKDATE=" + DBModule.RefineDatetime(BACKDATE) + "," +

                            "PORNAME='" + DBModule.RefineString(PORNAME) + "'," +
                            "POLNAME='" + DBModule.RefineString(POLNAME) + "'," +
                            "PODNAME='" + DBModule.RefineString(PODNAME) + "'," +
                            "DESTNAME='" + DBModule.RefineString(DESTNAME) + "'," +
                            "POISSNAME='" + DBModule.RefineString(POISSNAME) + "'," +
                            "PAYABLEATNAME='" + DBModule.RefineString(PAYABLEATNAME) + "'" +
                            ",CARRCODE='" + DBModule.RefineString(CARRCODE) + "'" +
                            ",SERIBILL='" + DBModule.RefineString(SERIBILL) + "'" +
                 " where ID=" + ID.ToString();
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("CusDoc", "edit", cn, trans);
        }
    }
    public void UpdateShipingIntruction(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_CusDoc";

        // build SQL statement
        strSQL = "Update " + strTableName + " set " +
                        "SI_ATTN='" + DBModule.RefineString(SI_ATTN) + "'," +
                        "SI_NOTED='" + DBModule.RefineString(SI_NOTED) + "'," +
                        "SI_COMMENT='" + DBModule.RefineString(SI_COMMENT) + "'," +
                        "SI_UPDATE=" + DBModule.RefineDatetime(SI_UPDATE) + " " +
             " where ID=" + ID.ToString();
        // run SQL statement
        DBModule.ExecuteNonQuery(strSQL, cn, trans);
        UserObjectsLog.SaveObjectActionLog("CusDoc", "edit", cn, trans);
        //GV.Response.Write(strSQL);
    }
    public void UpdateTranshipment(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_CusDoc";

        // build SQL statement
        strSQL = "Update " + strTableName + " set " +
                        "TRANS_ATTN='" + DBModule.RefineString(TRANS_ATTN) + "'," +
                        "TRANS_ACC='" + DBModule.RefineString(TRANS_ACC) + "'," +
                        "TRANS_UPDATE=" + DBModule.RefineDatetime(TRANS_UPDATE) + " " +
             " where ID=" + ID.ToString();
        // run SQL statement
        DBModule.ExecuteNonQuery(strSQL, cn, trans);
        UserObjectsLog.SaveObjectActionLog("CusDoc", "edit", cn, trans);
    }
    public void UpdateArrial(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_CusDoc";

        // build SQL statement
        strSQL = "Update " + strTableName + " set " +
                        "ARRIAL_ATTN='" + DBModule.RefineString(ARRIAL_ATTN) + "'," +
                        "ARRIAL_ACC='" + DBModule.RefineString(ARRIAL_ACC) + "'," +
                        "ARRIAL_UPDATE=" + DBModule.RefineDatetime(ARRIAL_UPDATE) + " " +
             " where ID=" + ID.ToString();
        // run SQL statement
        DBModule.ExecuteNonQuery(strSQL, cn, trans);
        UserObjectsLog.SaveObjectActionLog("CusDoc", "edit", cn, trans);
    }
    public void Delete(MySqlConnection cn, MySqlTransaction trans)
    {

        //Delete Shipp
        string strSQL = "DELETE FROM tbl_ShippingDetail WHERE CusdocID = " + this.ID.ToString() + ";";
        //Delete Tracking
        strSQL += "DELETE FROM tbl_Tracking WHERE CusdocID = " + this.ID.ToString() + ";";
        strSQL += "DELETE FROM tbl_Cusdoc WHERE ID = " + this.ID.ToString();
        DBModule.ExecuteNonQuery(strSQL, cn, trans);
        UserObjectsLog.SaveObjectActionLog("CusDoc", "delete", cn, trans);
    }
    public void Load(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "SELECT * FROM tbl_Cusdoc WHERE ID = " + ID.ToString() + " limit 0,1";
        //string strSQL = "Select tbl_Cusdoc.*, por.Name as PORNAME, POL.Name as POLNAME,Pod.Name as PODNAME,DEST.Name as DESTNAME " +
        //    ",PAYABLE.Name as PAYABLEATNAME,POISS.Name as POISSNAME " +
        //    " from tbl_Cusdoc LEFT JOIN tbl_ports por ON por.ID=tbl_Cusdoc.PORID" +
        //    " LEFT JOIN tbl_ports POL ON POL.ID=tbl_Cusdoc.POLID " +
        //    " LEFT JOIN tbl_ports Pod ON Pod.ID=tbl_Cusdoc.PodID " +
        //    " LEFT JOIN tbl_ports DEST ON DEST.ID=tbl_Cusdoc.DESTID " +
        //    " LEFT JOIN tbl_ports PAYABLE ON PAYABLE.ID=tbl_Cusdoc.PAYABLEAT " +
        //    " LEFT JOIN tbl_ports POISS ON POISS.ID=tbl_Cusdoc.POISS " +
        //    " WHERE tbl_Cusdoc.ID = " + ID.ToString() + " limit 0,1";

        DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        //Fill data into this object
        if (ds.Tables[0].Rows.Count > 0)
        {
            DataRow dr = ds.Tables[0].Rows[0];
            if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
            if (!dr.IsNull("FILENO")) FILENO = long.Parse(dr["FILENO"].ToString());
            if (!dr.IsNull("MBKGNO")) MBKGNO = dr["MBKGNO"].ToString();
            if (!dr.IsNull("HBLNO")) HBLNO = dr["HBLNO"].ToString();
            if (!dr.IsNull("MBLNO")) MBLNO = dr["MBLNO"].ToString();
            if (!dr.IsNull("HBLSHPR")) HBLSHPR = dr["HBLSHPR"].ToString();
            if (!dr.IsNull("HBLCNEE")) HBLCNEE = dr["HBLCNEE"].ToString();
            if (!dr.IsNull("HBLNTF")) HBLNTF = dr["HBLNTF"].ToString();
            if (!dr.IsNull("VSSL")) VSSL = dr["VSSL"].ToString();
            if (!dr.IsNull("VOYNO")) VOYNO = dr["VOYNO"].ToString();
            if (!dr.IsNull("PORID")) PORID = int.Parse(dr["PORID"].ToString());
            if (!dr.IsNull("POLID")) POLID = int.Parse(dr["POLID"].ToString());
            if (!dr.IsNull("SHONBOARD")) SHONBOARD = DateTime.Parse(dr["SHONBOARD"].ToString());
            if (!dr.IsNull("PodID")) PodID = int.Parse(dr["PodID"].ToString());
            if (!dr.IsNull("DESTID")) DESTID = int.Parse(dr["DESTID"].ToString());
            if (!dr.IsNull("DOGMN")) DOGMN = dr["DOGMN"].ToString();
            if (!dr.IsNull("TypeOfMovement")) TypeOfMovement = dr["TypeOfMovement"].ToString();
            if (!dr.IsNull("TOP")) TOP = dr["TOP"].ToString();
            if (!dr.IsNull("PAYABLEAT")) PAYABLEAT = long.Parse(dr["PAYABLEAT"].ToString());
            if (!dr.IsNull("POISS")) POISS = long.Parse(dr["POISS"].ToString());
            if (!dr.IsNull("NOORI")) NOORI = dr["NOORI"].ToString();
            if (!dr.IsNull("DOISS")) DOISS = DateTime.Parse(dr["DOISS"].ToString());
            if (!dr.IsNull("AGDLVR")) AGDLVR = dr["AGDLVR"].ToString();
            if (!dr.IsNull("CARRN")) CARRN = dr["CARRN"].ToString();
            if (!dr.IsNull("MBLSHPR")) MBLSHPR = dr["MBLSHPR"].ToString();
            if (!dr.IsNull("MBLCNEE")) MBLCNEE = dr["MBLCNEE"].ToString();
            if (!dr.IsNull("MBLNTF")) MBLNTF = dr["MBLNTF"].ToString();
            if (!dr.IsNull("BOOKER")) BOOKER = dr["BOOKER"].ToString();
            if (!dr.IsNull("CTRNO")) CTRNO = int.Parse(dr["CTRNO"].ToString());
            if (!dr.IsNull("CTRVALID")) CTRVALID = DateTime.Parse(dr["CTRVALID"].ToString());
            if (!dr.IsNull("CTRNAME")) CTRNAME = dr["CTRNAME"].ToString();

            if (!dr.IsNull("RATE")) RATE = double.Parse(dr["RATE"].ToString());
            if (!dr.IsNull("UNIT")) UNIT = dr["UNIT"].ToString();
            if (!dr.IsNull("CURR")) CURR = dr["CURR"].ToString();
            if (!dr.IsNull("DVALUE")) DVALUE = int.Parse(dr["DVALUE"].ToString());
            if (!dr.IsNull("STUFF")) STUFF = dr["STUFF"].ToString();
            if (!dr.IsNull("CreatedDate")) CreatedDate = DateTime.Parse(dr["CreatedDate"].ToString());
            if (!dr.IsNull("UserID")) UserID = int.Parse(dr["UserID"].ToString());

            if (!dr.IsNull("PORNAME")) PORNAME = dr["PORNAME"].ToString();
            if (!dr.IsNull("POLNAME")) POLNAME = dr["POLNAME"].ToString();
            if (!dr.IsNull("PODNAME")) PODNAME = dr["PODNAME"].ToString();
            if (!dr.IsNull("DESTNAME")) DESTNAME = dr["DESTNAME"].ToString();
            if (!dr.IsNull("PAYABLEATNAME")) PAYABLEATNAME = dr["PAYABLEATNAME"].ToString();
            if (!dr.IsNull("POISSNAME")) POISSNAME = dr["POISSNAME"].ToString();

            if (!dr.IsNull("MBLType")) MBLType = int.Parse(dr["MBLType"].ToString());
            if (!dr.IsNull("HBLType")) HBLType = int.Parse(dr["HBLType"].ToString());
            if (!dr.IsNull("TOPMBL")) TOPMBL = dr["TOPMBL"].ToString();
            if (!dr.IsNull("NOTE")) NOTE = dr["NOTE"].ToString();
            if (!dr.IsNull("BACKDATE")) BACKDATE = DateTime.Parse(dr["BACKDATE"].ToString());
            if (!dr.IsNull("SI_ATTN")) SI_ATTN = dr["SI_ATTN"].ToString();
            if (!dr.IsNull("SI_NOTED")) SI_NOTED = dr["SI_NOTED"].ToString();
            if (!dr.IsNull("SI_COMMENT")) SI_COMMENT = dr["SI_COMMENT"].ToString();
            if (!dr.IsNull("SI_UPDATE")) SI_UPDATE = DateTime.Parse(dr["SI_UPDATE"].ToString());
            if (!dr.IsNull("TRANS_ATTN")) TRANS_ATTN = dr["TRANS_ATTN"].ToString();
            if (!dr.IsNull("TRANS_ACC")) TRANS_ACC = dr["TRANS_ACC"].ToString();
            if (!dr.IsNull("TRANS_UPDATE")) TRANS_UPDATE = DateTime.Parse(dr["TRANS_UPDATE"].ToString());
            if (!dr.IsNull("ARRIAL_ATTN")) ARRIAL_ATTN = dr["ARRIAL_ATTN"].ToString();
            if (!dr.IsNull("ARRIAL_ACC")) ARRIAL_ACC = dr["ARRIAL_ACC"].ToString();
            if (!dr.IsNull("ARRIAL_UPDATE")) ARRIAL_UPDATE = DateTime.Parse(dr["ARRIAL_UPDATE"].ToString());
            if (!dr.IsNull("CARRCODE")) CARRCODE = dr["CARRCODE"].ToString();
            if (!dr.IsNull("SERIBILL")) SERIBILL = dr["SERIBILL"].ToString();
        }
    }
    public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
    {
        NumberOfPages = 0;
        ds = null;
        // build SQL statement
        //public string VSSL = "";//ten con tau
        //public string VOYNO = "";//so chuyen tau
        //public int PORID = 0;//dia diem nhan hang
        //public string PORNAME = ""; //EXT - Ten dia diem nhan hang
        //public int POLID = 0;//cang chat hang
        //public string POLNAME = "";//EXT - Ten dia diem chat hang
        //public DateTime SHONBOARD = DateTime.Now;//ngay len tau
        //public int PodID = 0;//cang giao hang
        //public string PODNAME = "";//EXT - Ten cang giao hang
        //public int DESTID = 0;// cang den
        //public string DESTNAME = "";//EXT - Ten cang den
        //string strSQL = "Select tbl_Cusdoc.* " +
        //", por.Name as PORNAME, POL.Name as POLNAME,Pod.Name as PODNAME,DEST.Name as DESTNAME " +
        //",PAYABLE.Name as PAYABLEATNAME,POISS.Name as POISSNAME " +
        //" from tbl_Cusdoc LEFT JOIN tbl_ports por ON por.ID=tbl_Cusdoc.PORID" +
        //" LEFT JOIN tbl_ports POL ON POL.ID=tbl_Cusdoc.POLID " +
        //" LEFT JOIN tbl_ports Pod ON Pod.ID=tbl_Cusdoc.PodID " +
        //" LEFT JOIN tbl_ports DEST ON DEST.ID=tbl_Cusdoc.DESTID " +
        //" LEFT JOIN tbl_ports PAYABLE ON PAYABLE.ID=tbl_Cusdoc.PAYABLEAT " +
        //" LEFT JOIN tbl_ports POISS ON POISS.ID=tbl_Cusdoc.POISS " +
        //" from tbl_Cusdoc where 1=1 ";
        string strSQL = "SELECT tbl_cusdoc.*, tbl_adminusers.Idprovince FROM tbl_cusdoc LEFT JOIN tbl_adminusers ON tbl_cusdoc.userid = tbl_adminusers.id where 1=1 ";
        if ((strIDs != "") && (strIDs != null))
        {
            if (strIDs != "All")
                strSQL += strIDs;
        }
        if ((OrderBy != null) && (OrderBy != ""))
            strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
        //GV.Response.Write("sql=" + strSQL);
        // run SQL statement
        if (NumOfItemsPerPage > 0)
        {
            ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
        }
        else
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
    }
    public static long GetNewFileNo(MySqlConnection cn, MySqlTransaction trans)
    {
        long retVal = 1;
        string sqlCommmand = "Select FileNo from tbl_CusDoc order by FileNo  desc limit 0,1";
        string newFileNo = DBModule.ExecuteQueryForOneResult(sqlCommmand, cn, trans);
        if (!string.IsNullOrEmpty(newFileNo))
        {
            retVal = long.Parse(newFileNo) + 1;
        }
        return retVal;
    }
    public static long IsFileNumberExisting(long fileNo, long currentID, MySqlConnection cn, MySqlTransaction trans)
    {
        long retVal = -1;
        string sqlCommand = "";
        // build SQL statement
        sqlCommand = "SELECT ID FROM tbl_CusDoc WHERE ID <> " + currentID +
            " and FileNo=" + fileNo + " limit 0,1";
        string temp = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
        // run SQL statement
        if (temp != null)
        {
            retVal = long.Parse(temp);
        }

        return retVal;
    }
    public static string GetStatusCostingSale(long fileNo, MySqlConnection cn, MySqlTransaction trans)
    {
        string retVal = "";
        string sqlCommand = "";
        DataSet ds = null;
        // build SQL statement
        sqlCommand = "SELECT FileNumber,InvoicesNo FROM tbl_cost_accounting WHERE FileNumber = " + fileNo + " limit 0,1";
        ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        if (ds.Tables[0].Rows.Count > 0)
        {
            if (ds.Tables[0].Rows[0]["InvoicesNo"].ToString().Length > 0)
                retVal = "Invoiced";
            else
                retVal = "Costed";
        }
        else
        {
            sqlCommand = "SELECT ID FROM tbl_customerofferprices WHERE CostNumber = " + fileNo + " limit 0,1";
            ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                retVal = "Costing";
        }
        return retVal;
    }


}

public class ShippingDetail
{
    public long ID = 0;
    public long CusdocID = 0;
    public string CONT = "";//so container          =15
    public string SEAL = "";//so chi                =16
    public string ContType = "";//loai container    =17    
    public string PKGS = "";//quy cach dong hang    =18
    public double GW = 0;//tong trong luong         =19
    public double MEAS = 0;//so khoi                =20
    public string TEMP = "";//nhiet do              =21
    public int VOL = 0;//So Luong



    public ShippingDetail() { }
    public void Save(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_ShippingDetail";

        if (ID <= 0) // new object, we insert new record to database
        {
            //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
            strSQL = "Insert into " + strTableName + " (" +
                         "`CusdocID`" +
                         ",`CONT`" +
                         ",`SEAL`" +
                         ",`ContType`" +
                         ",`PKGS`" +
                          ",`GW`" +
                          ",`MEAS`" +
                          ",`TEMP`" +
                          ",`VOL`" +
                         ") " +
                     " values(" +
                         "'" + CusdocID.ToString() + "'" +
                         "," + "'" + DBModule.RefineString(CONT) + "'" +
                         "," + "'" + DBModule.RefineString(SEAL) + "'" +
                         "," + "'" + DBModule.RefineString(ContType) + "'" +
                         "," + "'" + DBModule.RefineString(PKGS) + "'" +
                         "," + GW.ToString() +
                         "," + MEAS.ToString() +
                         "," + "'" + DBModule.RefineString(TEMP) + "'" +
                         "," + "'" + VOL + "'" +
                         ")";
            // run SQL statement
            //throw new Exception(strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            //GV.Response.Write(strSQL);
        }
        else // edit object, we update old record in database
        {
            // build SQL statement
            strSQL = "Update " + strTableName + " set " +
                        "CusdocID='" + CusdocID.ToString() + "'" +
                         ",CONT='" + DBModule.RefineString(CONT) + "'" +
                         ",SEAL='" + DBModule.RefineString(SEAL) + "'" +
                         ",ContType='" + DBModule.RefineString(ContType) + "'" +
                         ",PKGS='" + DBModule.RefineString(PKGS) + "'" +
                         ",GW = " + GW.ToString() +
                         ",MEAS = " + MEAS.ToString() +
                         ",TEMP='" + DBModule.RefineString(TEMP) + "'" +
                         ",VOL=" + VOL.ToString() + "" +
                " where ID=" + ID.ToString();
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            //GV.Response.Write(strSQL);
        }
    }
    public void Delete(MySqlConnection cn, MySqlTransaction trans)
    {

        string strSQL = "DELETE FROM tbl_ShippingDetail WHERE ID = " + this.ID.ToString();

        DBModule.ExecuteNonQuery(strSQL, cn, trans);
    }
    public void Load(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "SELECT * FROM tbl_ShippingDetail WHERE ID = " + ID.ToString() + " limit 0,1";

        DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        //Fill data into this object
        if (ds.Tables[0].Rows.Count > 0)
        {
            DataRow dr = ds.Tables[0].Rows[0];
            if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
            if (!dr.IsNull("CusdocID")) CusdocID = long.Parse(dr["CusdocID"].ToString());
            if (!dr.IsNull("CONT")) CONT = dr["CONT"].ToString();
            if (!dr.IsNull("SEAL")) SEAL = dr["SEAL"].ToString();
            if (!dr.IsNull("ContType")) SEAL = dr["ContType"].ToString();
            if (!dr.IsNull("PKGS")) SEAL = dr["PKGS"].ToString();
            if (!dr.IsNull("GW")) GW = double.Parse(dr["GW"].ToString());
            if (!dr.IsNull("MEAS")) MEAS = double.Parse(dr["MEAS"].ToString());
            if (!dr.IsNull("TEMP")) TEMP = dr["TEMP"].ToString();
            if (!dr.IsNull("VOL")) VOL = int.Parse(dr["VOL"].ToString());
        }
    }
    public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
    {
        NumberOfPages = 0;
        string strSQL = "";
        ds = null;
        // build SQL statement
        strSQL = "Select * from tbl_ShippingDetail where 1=1 ";
        if ((strIDs != "") && (strIDs != null))
        {
            if (strIDs != "All")
                strSQL += strIDs;
        }
        if ((OrderBy != null) && (OrderBy != ""))
            strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
        //GV.Response.Write("sql=" + strSQL);
        // run SQL statement
        if (NumOfItemsPerPage > 0)
        {
            ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
        }
        else
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
    }

    public static string GetGroupCatContType(long cusdocID)
    {
        string retVal = "";
        string sqlCommand = "SELECT REPLACE(GROUP_CONCAT(catContype),',',' & ') AS GroupCatContType FROM " +
            "(SELECT  CONCAT(SUM(VOL),' X ',ContType) AS catContype FROM tbl_shippingdetail WHERE CusdocID =" + cusdocID +
            " GROUP BY ContType) AS catType";
        string result = DBModule.ExecuteQueryForOneResult(sqlCommand, GV.cn, GV.trans);
        if (result != null)
        {
            if (result.IndexOf("0 X") == 0)
                result = result.Substring(3, result.Length - 3);
            retVal = result;
        }
        return retVal;
    }
    public static string GetTotalGroupCatContType(string strCondition)
    {
        string retVal = "";
        string sqlCommand = "SELECT REPLACE(GROUP_CONCAT(catContype),',',' & ') AS GroupCatContType FROM " +
            "(SELECT  CONCAT(SUM(VOL),' X ',ContType) AS catContype FROM tbl_shippingdetail WHERE CusdocID in (Select tbl_Cusdoc.ID FROM tbl_Cusdoc " +
            " LEFT JOIN tbl_adminusers ON tbl_cusdoc.userid = tbl_adminusers.id" +
            " WHERE 1=1 " + strCondition + ")" +
        " GROUP BY ContType) AS catType";
        string result = DBModule.ExecuteQueryForOneResult(sqlCommand, GV.cn, GV.trans);
        if (result != null)
            retVal = result;
        return retVal;
    }
    public static double GetTotalGWByDocID(long cusdocID)
    {
        double retVal = 0;
        string sqlCommand = "SELECT SUM(GW) AS sumGW FROM tbl_shippingdetail WHERE CusdocID =" + cusdocID;
        string result = DBModule.ExecuteQueryForOneResult(sqlCommand, GV.cn, GV.trans);
        if (!String.IsNullOrEmpty(result))
            retVal = double.Parse(result);
        return retVal;
    }
    public static double GetTotalMEASByDocID(long cusdocID)
    {
        double retVal = 0;
        string sqlCommand = "SELECT SUM(MEAS) AS sumMEAS FROM tbl_shippingdetail WHERE CusdocID =" + cusdocID;
        string result = DBModule.ExecuteQueryForOneResult(sqlCommand, GV.cn, GV.trans);
        if (!String.IsNullOrEmpty(result))
            retVal = double.Parse(result);
        return retVal;
    }
    public static double GetTotalVOLByDocID(long cusdocID)
    {
        double retVal = 0;
        string sqlCommand = "SELECT SUM(VOL) AS sumVOL FROM tbl_shippingdetail WHERE CusdocID =" + cusdocID;
        string result = DBModule.ExecuteQueryForOneResult(sqlCommand, GV.cn, GV.trans);
        if (!String.IsNullOrEmpty(result))
            retVal = double.Parse(result);
        return retVal;
    }
    public static int SIStatusSending(long cusdocID)
    {
        int retVal = 0;
        string sqlCommand = "SELECT ID FROM tbl_shippingdetail WHERE CusdocID =" + cusdocID + " and CONT <>'' and SEAL <>''";
        string result = DBModule.ExecuteQueryForOneResult(sqlCommand, GV.cn, GV.trans);
        if (!String.IsNullOrEmpty(result))
            retVal = int.Parse(result);
        return retVal;
    }
}

public class Tracking
{
    public long ID = 0;
    public long CusdocID = 0;
    public string Status = "";
    public string Location = "";
    public long LocationID = 0;
    public string Discharge = "";
    public long DischargeID = 0;
    public DateTime TrackingDate = DateTime.MinValue;
    public DateTime ETA = DateTime.MinValue;
    public string Vssl = "";//Ten chuyen tau
    public string Voyno = "";//So chuyen
    public int ETDAccept = 0;
    public int ETAAccept = 0;


    public Tracking() { }

    public void Save(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "";
        string strTableName = "tbl_Tracking";

        if (ID <= 0) // new object, we insert new record to database
        {
            //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
            strSQL = "Insert into " + strTableName + " (" +
                         "`CusdocID`" +
                         ",`Status`" +
                         ",`TrackingDate`" +
                         ",`ETA`" +
                         ",`Vssl`" +
                         ",`Voyno`" +
                         ",`Location`" +
                         ",`LocationID`" +
                         ",`Discharge`" +
                         ",`DischargeID`" +
                         ",`ETDAccept`" +
                         ",`ETAAccept`" +
                         ") " +
                     " values(" +
                         CusdocID.ToString() +
                         ",'" + DBModule.RefineString(Status) + "'" +
                         "," + DBModule.RefineDatetime(TrackingDate) + "" +
                         "," + DBModule.RefineDatetime(ETA) + "" +
                         ",'" + DBModule.RefineString(Vssl) + "'" +
                         ",'" + DBModule.RefineString(Voyno) + "'" +
                         ",'" + DBModule.RefineString(Location) + "'" +
                         "," + LocationID.ToString() +
                         ",'" + DBModule.RefineString(Discharge) + "'" +
                         "," + DischargeID.ToString() +
                         "," + ETDAccept.ToString() +
                         "," + ETAAccept.ToString() +
                         ")";
            // run SQL statement
            //throw new Exception(strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        else // edit object, we update old record in database
        {
            // build SQL statement
            strSQL = "Update " + strTableName + " set " +
                        "CusdocID=" + CusdocID.ToString() +
                        ",Status='" + DBModule.RefineString(Status) + "'" +
                        ",TrackingDate=" + DBModule.RefineDatetime(TrackingDate) + "" +
                        ",ETA=" + DBModule.RefineDatetime(ETA) + "" +
                        ",Vssl='" + DBModule.RefineString(Vssl) + "'" +
                        ",Voyno='" + DBModule.RefineString(Voyno) + "'" +
                        ",LocationID=" + LocationID.ToString() +
                        ",Location='" + Location.ToString() + "'" +
                        ",DischargeID=" + DischargeID.ToString() +
                        ",Discharge='" + Discharge.ToString() + "'" +
                        ",ETDAccept='" + ETDAccept.ToString() + "'" +
                        ",ETAAccept='" + ETAAccept.ToString() + "'" +
                " where ID=" + ID.ToString();
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
    }
    public void Delete(MySqlConnection cn, MySqlTransaction trans)
    {

        string strSQL = "DELETE FROM tbl_Tracking WHERE ID = " + this.ID.ToString();

        DBModule.ExecuteNonQuery(strSQL, cn, trans);
    }
    public void Load(MySqlConnection cn, MySqlTransaction trans)
    {
        string strSQL = "SELECT tbl_Tracking.* " +
            //" ,por.Name as Location FROM tbl_Tracking  LEFT JOIN tbl_ports por ON por.ID=tbl_Tracking.LocationID" +
        " FROM tbl_Tracking WHERE tbl_Tracking.ID = " + ID.ToString() + " limit 0,1";
        DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        //Fill data into this object
        if (ds.Tables[0].Rows.Count > 0)
        {
            DataRow dr = ds.Tables[0].Rows[0];
            if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
            if (!dr.IsNull("CusdocID")) CusdocID = long.Parse(dr["CusdocID"].ToString());
            if (!dr.IsNull("Status")) Status = dr["Status"].ToString();
            if (!dr.IsNull("Location")) Location = dr["Location"].ToString();
            if (!dr.IsNull("LocationID")) LocationID = long.Parse(dr["LocationID"].ToString());
            if (!dr.IsNull("TrackingDate")) TrackingDate = DateTime.Parse(dr["TrackingDate"].ToString());
            if (!dr.IsNull("Discharge")) Discharge = dr["Discharge"].ToString();
            if (!dr.IsNull("DischargeID")) DischargeID = long.Parse(dr["DischargeID"].ToString());
            if (!dr.IsNull("ETA")) ETA = DateTime.Parse(dr["ETA"].ToString());
            if (!dr.IsNull("Vssl")) Vssl = dr["Vssl"].ToString();
            if (!dr.IsNull("Voyno")) Voyno = dr["Voyno"].ToString();
            if (!dr.IsNull("ETDAccept")) ETDAccept = int.Parse(dr["ETDAccept"].ToString());
            if (!dr.IsNull("ETAAccept")) ETAAccept = int.Parse(dr["ETAAccept"].ToString());
        }
    }
    public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
    {
        NumberOfPages = 0;
        string strSQL = "";
        ds = null;
        // build SQL statement
        strSQL = "SELECT tbl_Tracking.*,por.Name as Location FROM tbl_Tracking  LEFT JOIN tbl_ports por ON por.ID=tbl_Tracking.LocationID where 1=1 ";
        if ((strIDs != "") && (strIDs != null))
        {
            if (strIDs != "All")
                strSQL += strIDs;
        }
        if ((OrderBy != null) && (OrderBy != ""))
            strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
        //GV.Response.Write("sql=" + strSQL);
        // run SQL statement
        if (NumOfItemsPerPage > 0)
        {
            ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
        }
        else
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
    }
    public static string GetETA(long cusdocID)
    {
        string retVal = "";
        string sqlCommand = "SELECT tbl_tracking.ETA FROM tbl_cusdoc,tbl_tracking WHERE tbl_cusdoc.ID=tbl_tracking.CusdocID " +
            " AND tbl_cusdoc.DESTID=tbl_tracking.DischargeID" +
            " AND tbl_cusdoc.ID=" + cusdocID + " Order By tbl_tracking.ID desc limit 0,1";
        DataTable dtb = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans).Tables[0];
        if (dtb.Rows.Count > 0)
        {
            retVal = dtb.Rows[0]["ETA"].ToString();
        }
        return retVal;
    }
    public static int GetATD1(long cusdocID, MySqlConnection cn, MySqlTransaction trans)
    {
        int retVal = 0;
        string sqlCommand = "SELECT count(tbl_tracking.Status) as nStatus FROM tbl_cusdoc,tbl_tracking WHERE tbl_cusdoc.ID=tbl_tracking.CusdocID " +
            " AND tbl_tracking.Status like '%ATD 1%'" +
            " AND tbl_tracking.CusdocID=" + cusdocID;
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        if (ds != null) retVal = int.Parse(ds.Tables[0].Rows[0]["nStatus"].ToString());
        return retVal;
    }
    public static int NotificationETD(long cusdocID)
    {
        int retVal = 0;
        string sqlCommand = "SELECT tbl_tracking.TrackingDate FROM tbl_tracking,tbl_cusdoc WHERE tbl_cusdoc.ID=tbl_tracking.CusdocID " +
            " AND tbl_tracking.Location <> tbl_cusdoc.POLNAME " +
            " AND tbl_tracking.ETDAccept <> 1 " +
            " AND tbl_cusdoc.ID=" + cusdocID + " limit 0,1";
        DataTable dtb = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans).Tables[0];
        if (dtb.Rows.Count > 0 && DateTime.Now > DateTime.Parse(dtb.Rows[0][0].ToString()).AddDays(1))
        {
            retVal = 1;
        }
        return retVal;
    }
    public static int NotificationETA(long cusdocID)
    {
        int retVal = 0;
        string sqlCommand = "SELECT tbl_tracking.ETA FROM tbl_cusdoc,tbl_tracking WHERE tbl_cusdoc.ID=tbl_tracking.CusdocID " +
            " AND tbl_cusdoc.DESTID=tbl_tracking.DischargeID" +
             " AND tbl_tracking.ETAAccept <> 1 " +
            " AND tbl_cusdoc.ID=" + cusdocID;
        DataTable dtb = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans).Tables[0];
        if (dtb.Rows.Count > 0 && DateTime.Parse(dtb.Rows[0][0].ToString()) > DateTime.MinValue)
        {
            if (DateTime.Now > DateTime.Parse(dtb.Rows[0][0].ToString()).AddDays(-1))
                retVal = 1;
        }
        return retVal;
    }
}